/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package gcs.Dados;

import gcs.Negocio.Actividade;
import gcs.Negocio.Contracto;
import gcs.Negocio.Prestacao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Samuel
 */
public class ContratoDB {
    
    Connection con;
    ConnDB c;

    public ContratoDB(){
       try {
           con = new ConnDB().Connect();
           //empresas
           Statement st = con.createStatement();
           ResultSet rs = st.executeQuery("SELECT COUNT(cod_contrato) FROM Contratos");
           while (rs.next()) {
               Contracto.setContador(rs.getInt(1));
           }
           //Servicos
           st = con.createStatement();
           rs = st.executeQuery("SELECT COUNT(cod_p) FROM Prestacoes");
           while (rs.next()) {
               Prestacao.setContador(rs.getInt(1));
           }
       } catch (SQLException ex) {
           Logger.getLogger(ContratoDB.class.getName()).log(Level.SEVERE, null, ex);
       }
   }



    public TreeMap<String,Contracto> getContr(){
        TreeMap<String,Contracto> mc = new TreeMap<String,Contracto>();
        TreeMap<String,Actividade> la = null;
        ArrayList<Prestacao> lp;
        Contracto c;
        Actividade a;
        Prestacao p;
        Statement st,st1, st2;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Contratos");
            while (rs.next()){
                c= new Contracto(true);
                c.setCodContracto(rs.getString(1));
                c.setCodCliente(rs.getString(2));
                c.setCodUtilizador(rs.getString(3));
                c.setCodEmpresa(rs.getString(4));
                c.setCodServico(rs.getString(5));
                Date dt = rs.getDate(6);
                GregorianCalendar gc = new GregorianCalendar();
                gc.setTime(dt);
                c.setDatainicio(gc);
                c.setTotal(rs.getFloat(7));
                c.setActivo(rs.getBoolean(8));
                st1 = con.createStatement();
                ResultSet rs1 = st1.executeQuery("SELECT cod_actividade,nome,descricao,preco,activo,cod_s FROM Actividades a, Contr_Acts ca WHERE a.cod_actividade = ca.cod_act AND ca.cod_contr='"+c.getCodContracto()+"'");
                la = new TreeMap<String, Actividade>();
                while(rs1.next()){
                    a= new Actividade(true);
                    a.setcodAct(rs1.getString(1));
                    a.setNome(rs1.getString(2));
                    a.setDes(rs1.getString(3));
                    a.setPreco(rs1.getFloat(4));
                    a.setActivo(rs1.getBoolean(5));
                    la.put(a.getcodAct(),a);
                }
                c.setListaActividade(la);
                st2 = con.createStatement();
                ResultSet rs2 = st2.executeQuery("SELECT * FROM Prestacoes WHERE cod_c='"+c.getCodContracto()+"'");
                lp = new ArrayList<Prestacao>();
                while(rs2.next()){
                    p = new Prestacao(true);
                    p.setcodPres(rs2.getString(1));
                    Date dtL = rs2.getDate(2);
                    GregorianCalendar gc1 = new GregorianCalendar();
                    gc1.setTime(dtL);
                    p.setDataLimite(gc1);
                    p.setMontante(rs2.getFloat(3));
                    p.setPago(rs2.getBoolean(4));
                    lp.add(p);
                }
                c.setListaPrestacoes(lp);
                mc.put(c.getCodContracto(),c);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return mc;
    }

    public Contracto getContr(String cod_cont){ //Contrato c
        TreeMap<String,Actividade> la = null;
        ArrayList<Prestacao> lp;
        Contracto c = null;
        Actividade a;
        Prestacao p;
        Statement st,st1,st2;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Contratos WHERE cod_contrato='"+cod_cont+"'");
            if (rs.next()){
                c= new Contracto(true);
                c.setCodContracto(rs.getString(1));
                c.setCodCliente(rs.getString(2));
                c.setCodUtilizador(rs.getString(3));
                c.setCodEmpresa(rs.getString(4));
                c.setCodServico(rs.getString(5));
                Date dt = rs.getDate(6);
                GregorianCalendar gc = new GregorianCalendar();
                gc.setTime(dt);
                c.setDatainicio(gc);
                c.setTotal(rs.getFloat(7));
                c.setActivo(rs.getBoolean(8));
                st1 = con.createStatement();
                ResultSet rs1 = st1.executeQuery("SELECT cod_actividade,nome,descricao,preco,activo,cod_s FROM Actividades a, Contr_Acts ca WHERE a.cod_actividade = ca.cod_act AND ca.cod_contr='"+c.getCodContracto()+"'");
                la = new TreeMap<String, Actividade>();
                while(rs1.next()){
                    a= new Actividade(true);
                    a.setcodAct(rs1.getString(1));
                    a.setNome(rs1.getString(2));
                    a.setDes(rs1.getString(3));
                    a.setPreco(rs1.getFloat(4));
                    a.setActivo(rs1.getBoolean(5));
                    la.put(a.getcodAct(),a);
                }
                c.setListaActividade(la);
                st2 = con.createStatement();
                ResultSet rs2 = st2.executeQuery("SELECT * FROM Prestacoes WHERE cod_c='"+c.getCodContracto()+"'");
                lp = new ArrayList<Prestacao>();
                while(rs2.next()){
                    p = new Prestacao(true);
                    p.setcodPres(rs2.getString(1));
                    Date dtL = rs2.getDate(2);
                    GregorianCalendar gc1 = new GregorianCalendar();
                    gc1.setTime(dtL);
                    p.setDataLimite(gc1);
                    p.setMontante(rs2.getFloat(3));
                    p.setPago(rs2.getBoolean(4));
                    lp.add(p);
                }
                c.setListaPrestacoes(lp);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return c;
    }


    public void insereContr(Contracto c){
        TreeMap<String,Actividade> la = null;
        ArrayList<Prestacao> lp;
        Actividade a;
        Statement st,st1,st2;
        try {
            st = con.createStatement();
            String cod_c = c.getCodContracto();
            String cod_cl = c.getCodCliente();
            String cod_f = c.getCodUtilizador();
            String cod_e = c.getCodEmpresa();
            String cod_s = c.getCodServico();
            float tot = c.getTotal();
            GregorianCalendar gc = c.getDatainicio();
            Date d = gc.getTime();

            //java.sql.Date data = new java.sql.Date(d.getYear(), d.getMonth(), d.getDay());
            java.sql.Date data = new java.sql.Date(d.getTime());
            boolean activo = c.isActivo();
            st.executeUpdate("INSERT INTO Contratos " +
            "VALUES ('"+cod_c+"','"+cod_cl+"', '"+cod_f+"','"+cod_e+"', '"+cod_s+"', '"+data+"', "+tot+","+activo+")");
            for(Actividade act : c.getListaActividades().values()){
                st1 = con.createStatement();
                String cod_a = act.getcodAct();
                st1.executeUpdate("INSERT INTO Contr_Acts " +
                "VALUES ('"+cod_c+"','"+cod_a+"')");
            }
            for(Prestacao pr : c.getListaPrestacoes()){
                st2 = con.createStatement();
                String cod_p = pr.getcodPres();
                GregorianCalendar gc1 = pr.getDataLimite();
                //System.out.println(gc1.getTime());
                Date d1 = gc1.getTime();
                java.sql.Date data1 = new java.sql.Date(d1.getTime());
                float total = pr.getMontante();
                boolean b = pr.getPago();
                st2.executeUpdate("INSERT INTO Prestacoes " +
                "VALUES ('"+cod_p+"','"+data1+"',"+total+","+b+", '"+cod_c+"')");
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    public boolean remContr(Contracto c){
        try {
            String sql = "UPDATE Contratos SET activo =? WHERE cod_c = ?";
            PreparedStatement prest = con.prepareStatement(sql);
            prest.setBoolean(1,c.isActivo());
            prest.setString(2, c.getCodContracto());
            prest.executeUpdate();
        }
        catch (SQLException ex) {
            //Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }



    public TreeMap<String,Contracto> getContrCli(String cod_cli){
        TreeMap<String,Contracto> mc = new TreeMap<String,Contracto>();
        TreeMap<String,Actividade> la = null;
        ArrayList<Prestacao> lp;
        Contracto c;
        Actividade a;
        Prestacao p;
        Statement st,st1, st2;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Contratos WHERE cod_cl ='"+cod_cli+"'");
            while (rs.next()){
                c= new Contracto(true);
                c.setCodContracto(rs.getString(1));
                c.setCodCliente(rs.getString(2));
                c.setCodUtilizador(rs.getString(3));
                c.setCodEmpresa(rs.getString(4));
                c.setCodServico(rs.getString(5));
                Date dt = rs.getDate(6);
                GregorianCalendar gc = new GregorianCalendar();
                gc.setTime(dt);
                c.setDatainicio(gc);
                c.setTotal(rs.getFloat(7));
                c.setActivo(rs.getBoolean(8));
                st1 = con.createStatement();
                ResultSet rs1 = st1.executeQuery("SELECT cod_actividade,nome,descricao,preco,activo,cod_s FROM Actividades a, Contr_Acts ca WHERE a.cod_actividade = ca.cod_act AND ca.cod_contr='"+c.getCodContracto()+"'");
                la = new TreeMap<String, Actividade>();
                while(rs1.next()){
                    a= new Actividade(true);
                    a.setcodAct(rs1.getString(1));
                    a.setNome(rs1.getString(2));
                    a.setDes(rs1.getString(3));
                    a.setPreco(rs1.getFloat(4));
                    a.setActivo(rs1.getBoolean(5));
                    la.put(a.getcodAct(),a);
                }
                c.setListaActividade(la);
                st2 = con.createStatement();
                ResultSet rs2 = st2.executeQuery("SELECT * FROM Prestacoes WHERE cod_c='"+c.getCodContracto()+"'");
                lp = new ArrayList<Prestacao>();
                while(rs2.next()){
                    p = new Prestacao(true);
                    p.setcodPres(rs2.getString(1));
                    Date dtL = rs2.getDate(2);
                    GregorianCalendar gc1 = new GregorianCalendar();
                    gc1.setTime(dtL);
                    p.setDataLimite(gc1);
                    p.setMontante(rs2.getFloat(3));
                    p.setPago(rs2.getBoolean(4));
                    lp.add(p);
                }
                c.setListaPrestacoes(lp);
                mc.put(c.getCodContracto(),c);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return mc;
    }

    public void insereActContr(Contracto c){// ou Contrato c ou TreeMap<String,Actividade> la
        Statement st;
        try {
            st = con.createStatement();
            String cod_c = c.getCodContracto();
            removeActContr(c);
            for(Actividade a : c.getListaActividades().values()){
                st.executeUpdate("INSERT INTO Contr_Acts " +
                "VALUES ('"+cod_c+"','"+a.getcodAct()+"')");
            }
            for(Prestacao pr : c.getListaPrestacoes()){
                actUpdate(pr);
            }
            String sql = "UPDATE Contratos SET total =? WHERE cod_contrato = ?";
            PreparedStatement prest = con.prepareStatement(sql);
            prest.setFloat(1,c.getTotal());
            prest.setString(2, cod_c);
            prest.executeUpdate();
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    public void removeActContr(Contracto c){// ou Contrato c ou TreeMap<String,Actividade> la
        Statement st;
        try {
            st = con.createStatement();
            String cod_c = c.getCodContracto();
            st.executeUpdate("DELETE FROM Contr_Acts WHERE cod_contr = '"+cod_c+"'");
        }
        catch (SQLException ex) {
            Logger.getLogger(ConnDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



    public ArrayList<Prestacao> getPrestacoes(String cod_c){ //Contrato c
        ArrayList<Prestacao> lp = new ArrayList<Prestacao>();
        Statement st;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Prestacoes WHERE cod_c = '"+cod_c+"'");
            while (rs.next()){
                Prestacao p=new Prestacao(true);
                p.setcodPres(rs.getString(1));
                Date dt = rs.getDate(2);
                GregorianCalendar gc = new GregorianCalendar();
                gc.setTime(dt);
                p.setDataLimite(gc);
                p.setMontante(rs.getFloat(3));
                p.setPago(rs.getBoolean(4));
                lp.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lp;
    }

    public boolean actUpdate(Prestacao p){
        try {
            String sql = "UPDATE Prestacoes SET montante =?, paga=? WHERE cod_p = ?";
            PreparedStatement prest = con.prepareStatement(sql);
            prest.setFloat(1, p.getMontante());
            prest.setBoolean(2, p.getPago());
            prest.setString(3, p.getcodPres());
            prest.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ContratoDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }

    public void inactivaContracto(Contracto c){
        try {
            String sql = "UPDATE Contratos SET activo = false";
            PreparedStatement cont = con.prepareStatement(sql);
            cont.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ContratoDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



}
